package databasetranslation;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import models.DatabaseModel;
import database.SQLServer;

/**
 * This class represents a list of entries. Manipulations are translated into SQL statements. The manipulations supported are:
 * <li>
 * 		<ol>add</ol>
 * 		<ol>replace</ol>
 * 		<ol>delete</ol>
 * </li>
 */
abstract public class DatabaseList<Model extends DatabaseModel> {
	
	private List<Model> entries = new ArrayList<Model>();
	private SQLServer server;
	private String tableName;
	
	/**
	 * Constructor, This function sets the server and the tablename
	 * 
	 * @param server ,server for database communication
	 * @param tableName ,name of the table that is source for the list.
	 */
	public DatabaseList(SQLServer server, String tableName) {
		this.server = server;
		this.tableName = tableName;
	}
	
	/**
	 * This function adds an object to the database and the local list.
	 * 
	 * @param entry object<Model> to add 
	 */
	public void add(Model entry) throws SQLException {
		insertIntoDB(entry);
		entry.setId(getIdFromDatabase(entry));
		entries.add(entry);
	}
	
	/**
	 * This function delete's an objects in the database and the local list.
	 * 
	 * @param entry object<Model> to delete 
	 */
	public void delete(Model entry) throws SQLException {
		deleteFromDB(entry);
		entries.remove(entry);	
	}
	
	/**
	 * This function updates the database with the updated values of the given entry.
	 * (Note: you can't replace the model, because other models hold references to the old model.)
	 * @param newEntry entry to update.
	 */
	public void replace(Model newEntry) throws SQLException {
		updateIntoDB(newEntry);	
	}
	
	/**
	 * Search for the id number of an object in the database.
	 * 
	 * @param entry is an DatabaseModel witch id is searched in this function.
	 * @return <code>int</code> of the id number, if there is no connection with the database the returned id is -2. 
	 * else if the object is not found in the database the returned id value is 0;
	 */
	private int getIdFromDatabase(Model entry) throws SQLException {
		int id = 0;
		ResultSet rs = server.select("SELECT id FROM " + tableName + " WHERE " + entry.getSelectString() + ";");
		if (rs == null) {
			// TODO: remove when database is tested.
			return (int) - Math.ceil(Math.random()*100);
		}
		while (rs.next()) {
			id = rs.getInt("id");
		}
		server.disconnect();
		return id;
	}
	
	/**
	 * Search for an entry with the given id number. Throws a RuntimeException if not found.
	 * 
	 * @param id number of the requested object
	 * @return Model entry with the requested id number. if id number is not found then the return value is <code>null</code>.
	 * (Warning returns object and not a copy)
	 */
	public Model getEntry(int id){
		for(Model entry : entries){
			if(entry.getId() == id){
				return entry;
			}
		}
		throw new RuntimeException("Client with id " + id + " not found!");
	}

	/**
	 * Discards all data in this DatabaseList and reloads it from the database.
	 * Warning: other lists must be re-imported too!
	 * @throws SQLException
	 */
	public void importList() throws SQLException {		
		entries = createListFromDatabase();				
	}
	
	/**
	 * Load the data from the database and compare it to the data in this list.
	 * @return <code>true<code> if the database is in sync with this list.
	 * @throws SQLException
	 */
	public boolean isEqualToDatabase() throws SQLException {
		List<Model> dbList = createListFromDatabase();			
		return this.equals(dbList);
	}
	
	/**
	 * Load the data in the database into a new List<Model>.
	 * @return
	 * @throws SQLException
	 */
	private List<Model> createListFromDatabase() throws SQLException{
		List<Model> dbList = new ArrayList<Model>();				
		ResultSet rs = server.select("SELECT * FROM " + tableName + ";");
		if (rs == null) {
			// Reason: dummyServer returns null
			// TODO: remove when database is fully tested
			return dbList;
		}
		while (rs.next()) {
			dbList.add(convertResultSetToModel(rs));
		}
		server.disconnect();
		
		return dbList;
	}

	/**
	 * Create a Model from the current selected ResultSet row
	 * 
	 * @param rs ResultSet with a selected row of a Payment
	 * @return the Model created from the ResultSet
	 */
	abstract public Model convertResultSetToModel(ResultSet rs) throws SQLException;
	
	/**
	 * Clears the list in the database that corresponds to this list.
	 */
	public void clearDatabaseTable() throws SQLException {
		server.update("TRUNCATE TABLE " + tableName + ";");
	}
	
	/**
	 * Removes an entry from the database.
	 * 
	 * @param object<Model> to delete
	 */
	private void deleteFromDB(Model deleteEntry) throws SQLException {
		server.update("DELETE FROM " + tableName + " WHERE id='" + deleteEntry.getId() + "';");
	}

	/**
	 * Inserts an object into the database.
	 * 
	 * @param new object<Model>
	 */
	private void insertIntoDB(Model newEntry) throws SQLException {
		server.update("INSERT INTO " + tableName + " SET id=null, " + newEntry.getDataString() + ";");
	}

	/**
	 * Execute's a replacement of two object in the database.
	 * 
	 * @param old object<Model>
	 * @param new object<Model>
	 */
	private void updateIntoDB(Model newEntry) throws SQLException {
		server.update("UPDATE " + tableName + " SET " + newEntry.getDataString() + " WHERE id='" + newEntry.getId() + "'");
	}
	
	/**
	 * Check if the given list contains the same data as an other list.
	 * 
	 * @param other List<Model> List to compare to
	 * @return <code>true</code> if equal
	 */
	public boolean equals(List<Model> other) {
		Set<Model> thisSet = new HashSet<Model>(this.entries);
		Set<Model> otherSet = new HashSet<Model>(other);
		return thisSet.equals(otherSet);
	}
		
	/**
	 * Get a copy of the list of entries.
	 * @return List of entries. 
	 */
	public List<Model> getEntries() {
		return new ArrayList<Model>(entries);
	}
	
	public int getSize() {
		return entries.size();
	}
	
}